package com.young.dynamicexcel.common.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.*;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Date;

/**
 * @DESCRIPTION: 动态导入导出工具
 * @USER: Young
 */
public class DynamicExcelUtil {

    static SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    //oracle查询数据库表结构sql
    private static final String getTableColumnsOracle = "SELECT A.DATA_TYPE \"data_type\", B.COLUMN_NAME \"column_name\", substr(B.COMMENTS,0,decode(instr(B.COMMENTS,'@'),NULL,255,0,255,instr(B.COMMENTS,'@'))-1) \"column_comment\"" +
            " FROM USER_TAB_COLUMNS A" +
            " LEFT JOIN USER_COL_COMMENTS B" +
            " ON B.TABLE_NAME = A.TABLE_NAME" +
            " AND A.COLUMN_NAME = B.COLUMN_NAME" +
            " WHERE A.TABLE_NAME = ?" +
            "order by b.COLUMN_NAME";
    //mysql查询数据库表结构sql
    //private static final String getTableColumnsMysql = "select column_name,column_comment,data_type,column_type,is_nullable from information_schema.columns where table_name=? and table_schema=? order by ORDINAL_POSITION asc";
    private static final String getTableColumnsMysql = "SELECT a.column_name, a.column_comment, a.data_type, a.column_type, a.is_nullable, CASE WHEN b.COLUMN_NAME = a.column_name THEN 'true' ELSE 'false' END is_pk" +
            " FROM" +
            " information_schema. COLUMNS a" +
            " JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.table_name = b.table_name" +
            " WHERE" +
            " a.table_name = ?" +
            " AND a.table_schema = (select database())" +
            " ORDER BY" +
            " a.ORDINAL_POSITION ASC";

    /**
     * 根据数据库表名，查数据库结构及数据，导出excel
     * @param tableName 数据库表标识
     * @param fileName  导出的文件名
     * @param isTemplate    是否是导出模板，是的话就不填充数据
     * @param dataSource    数据库dataSource，用来获取数据库连接，执行sql
     * @param request
     * @param response
     * @throws SQLException
     * @throws IOException
     */
    public static void createExcelByDatabase(String tableName, String fileName, boolean isTemplate, DataSource dataSource, HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException {
        fileName = fileName +".xls";

        //获取连接
        Connection connection = dataSource.getConnection();

        //创建簿
        Workbook workbook = new XSSFWorkbook();

        //解析表结构，构建excel表头数据
        buildExcelHead(workbook, tableName, connection);

        //构建数据
        if(!isTemplate){

            appendExcelData(tableName, connection, workbook);
        }

        //设置响应头
        DynamicExcelUtil.setResponseHeader(request, response, fileName);
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 根据数据库表名查数据库结构，生成excel表头。表头一共有三行：表字段标识、数据类型、字段说明
     * @param workbook
     * @param tableName
     * @param connection
     * @throws SQLException
     */
    private static void buildExcelHead(Workbook workbook, String tableName, Connection connection) throws SQLException {

        List<Map<String, String>> excelHeadDatas = new ArrayList<Map<String, String>>();
        //mysql获取数据库表结构
        PreparedStatement ps = connection.prepareStatement(getTableColumnsMysql);
        ps.setString(1, tableName);
        /*//oracle获取数据库表结构
        PreparedStatement ps = connection.prepareStatement(getTableColumnsOracle);
        ps.setString(1, tableName);*/
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
//            System.out.println(rs.getString("data_type") + " | " + rs.getString("column_name") + " | " + rs.getString("column_comment"));
            Map<String, String> map = new HashMap<String, String>();
            map.put("columnName", rs.getString("column_name"));
            map.put("dataType", rs.getString("data_type"));
            map.put("desc", rs.getString("column_comment"));
            excelHeadDatas.add(map);
        }

        //后续可以在此做字段过滤，将不需要导出的数据过滤掉
//        excelHeadDatas.stream().forEach(l -> {
//            System.out.println(l.get("columnName") + "," + l.get("dataType") + "," + l.get("desc"));
//        });

        Sheet sheet = workbook.createSheet();
        //设置表头样式
        CellStyle style = getHeadCellStyle(workbook);

        for (int rowNum = 0; rowNum < 3; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < excelHeadDatas.size(); cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellStyle(style);
                if (rowNum == 0){
                    cell.setCellValue(excelHeadDatas.get(cellNum).get("columnName"));
                }else if (rowNum == 1){
                    cell.setCellValue(excelHeadDatas.get(cellNum).get("dataType"));
                }else if (rowNum == 2){
                    cell.setCellValue(excelHeadDatas.get(cellNum).get("desc"));
                }
            }
        }

    }

    /**
     * 根据数据库表名查数据，并插入excel
     * @param tableName
     * @param connection
     * @param workbook
     * @throws SQLException
     */
    private static void appendExcelData(String tableName, Connection connection, Workbook workbook) throws SQLException {
        PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM " + tableName);
        ResultSet rs2 = ps2.executeQuery();
        ResultSetMetaData md = rs2.getMetaData();
        Sheet sheet = workbook.getSheetAt(0);
        //获取数据cell样式
        CellStyle style = getDataCellStyle(workbook);
        while(rs2.next()){

            Row row = sheet.createRow(sheet.getLastRowNum()+1);
            for(int i =0; i<md.getColumnCount(); i++){//遍历此行的所有列
                String cols_name = md.getColumnName(i + 1);//取出列名
                String val = rs2.getString(cols_name);
                Cell cell = row.createCell(i);
                cell.setCellStyle(style);
                cell.setCellValue(val);
            }
        }
    }

    /**
     * 生成excel
     *
     * @param excelName 生成excel的名称
     * @param headList  选择需要导出的字段集合。映射：实体字段标识、字段说明
     * @param dataList  数据集
     * @param response
     * @param <T>
     */
    public static <T> void createExcelByEntityData(String excelName, Map<String, String> headList, List<T> dataList, HttpServletRequest request, HttpServletResponse response) {

//        String fileName = excelName + "-" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xlsx";
        String fileName = excelName + ".xlsx";
        try {

            SXSSFWorkbook workbook = new SXSSFWorkbook();
            //创建表格头
            buildExcelHead(workbook, headList, excelName);
            //填充数据
            appendExcelData(workbook, headList, dataList);


            setResponseHeader(request,response,fileName);
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建表头，根据headmap里的映射构建，包含两行：实体字段标识、字段说明
     *
     * @param workbook
     * @param headMap
     */
    private static void buildExcelHead(SXSSFWorkbook workbook, Map<String, String> headMap, String excelName) {
        //设置表头样式
        CellStyle style = getHeadCellStyle(workbook);
        Sheet sheet = workbook.createSheet(excelName);
        Row headRow = sheet.createRow(0);
        Row headRow2 = sheet.createRow(1);
        int cellNum = 0;

        for (Map.Entry<String, String> head : headMap.entrySet()) {
            Cell cell = headRow.createCell(cellNum);
            cell.setCellStyle(style);
            cell.setCellValue(head.getValue());

            Cell cell1 = headRow2.createCell(cellNum);
            cell1.setCellStyle(style);
            cell1.setCellValue(head.getKey());

            cellNum++;
        }
    }

    /**
     * 填充数据
     *
     * @param workbook
     * @param headList
     * @param dataList
     * @param <T>
     */
    private static <T> void appendExcelData(SXSSFWorkbook workbook, Map<String, String> headList, List<T> dataList) {
        Sheet sheet = workbook.getSheetAt(0);
        //设置表格样式
        CellStyle style = getDataCellStyle(workbook);

        int lastRowNum = sheet.getLastRowNum() + 1;
        for (T t : dataList) {
            Row row = sheet.createRow(lastRowNum);
            int cellNum = 0;
            for (Map.Entry<String, String> head : headList.entrySet()) {
                Cell cell = row.createCell(cellNum);
                cell.setCellStyle(style);
                Object methodVal = getMethodVal(t.getClass(), t, head.getKey());
                if (methodVal == null) {
                    methodVal = "";
                }
                if (methodVal instanceof Integer) {
                    cell.setCellValue((Integer) methodVal);
                } else if (methodVal instanceof Date) {

                    String dateToStr = formatter.format(methodVal);
                    cell.setCellValue(dateToStr);
                } else if (methodVal instanceof LocalDateTime){
                    String dateToStr = ((LocalDateTime) methodVal).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                    cell.setCellValue(dateToStr);
                }else{
                    cell.setCellValue(methodVal.toString());
                }
                cellNum++;
            }
            lastRowNum++;
        }
    }

    /**
     * 通过数据库字段解析excel头，写入数据
     * @param tableName 数据库表标识
     * @param fileInputStream   excel文件输入流
     * @param dataSource    数据库dataSource，用来获取数据库连接，执行sql
     * @throws SQLException
     * @throws IOException
     * @throws ParseException
     */
    public static void insertExcelDataByDatabase(String tableName, InputStream fileInputStream, DataSource dataSource) throws SQLException, IOException, ParseException {
        //获取连接
        Connection connection = dataSource.getConnection();

        //获取工作薄
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //所有字段和类型的集合
        LinkedHashMap<String, String> columns = new LinkedHashMap<>();
        //记录一行有多少单元格
        int cellCount = 0;
        //获取0-字段名称、1-字段类型、2-字段描述
        Row rowColumnName = sheet.getRow(0);
        Row rowDataType = sheet.getRow(1);
        if (rowColumnName != null && rowDataType != null){
            //获取一行中有多少个单元格
            cellCount = rowColumnName.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                //获取单元
                Cell cellColumnName = rowColumnName.getCell(cellNum);
                Cell cellDataType = rowDataType.getCell(cellNum);
                if (cellColumnName != null){
                    //获取类型
                    String cellColumnNameValue = cellColumnName.getStringCellValue();
                    String cellDataTypeValue = cellDataType.getStringCellValue();
                    columns.put(cellColumnNameValue, cellDataTypeValue);
//                    System.out.print(cellColumnNameValue+","+ cellDataTypeValue + " | ");
                }
            }
        }

        //获取表中的内容,从第4行开始是excel中需要插入数据库的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 3; rowNum < rowCount; rowNum++) {
            //一行记录所有值的集合
            List<String> values = new ArrayList<>();
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null){
                //读取列
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//                    System.out.print("【" + (rowNum+1) + "-" + (cellNum+1) + "】");

                    Cell cell = rowData.getCell(cellNum);
                    //匹配列的数据类型
                    if (cell != null){
                        String cellValue = getCellValueString(cell);
                        values.add(cellValue);
                    }
                }
            }
            //将数据插入数据库
            //组装所有的插入字段
            String inserts = "";
            //组装所有的插入值的占位符
            String v = "";
            List<String> types = new ArrayList<>();
            for (String s : columns.keySet()) {
//                System.out.println("key=" + s + " value=" + columns.get(s));
                inserts = inserts + s + ",";
                v = v + "?,";
                types.add(columns.get(s));
            }

            String insertSql = "replace into "+ tableName +"("+inserts.substring(0, inserts.lastIndexOf(","))+")values("+v.substring(0, v.lastIndexOf(","))+")";
//            System.out.println(insertSql);

            PreparedStatement ps = connection.prepareStatement(insertSql);
            //构造真正的需要插入的值
            for (int i = 1; i<= types.size(); i++){
                String type = types.get(i-1);
                String value = values.get(i-1);
//                System.out.println(type + "-" + value);
                if (type.contains("varchar")){//字符串
                    ps.setString(i, value);
                }else if (type.contains("int") || type.contains("number")){//数字
                    ps.setInt(i, Integer.parseInt(value));
                }else if (type.contains("date")){//日期
                    if (!value.isEmpty()){
                        ps.setTimestamp(i, new Timestamp(
                                formatter.parse(value).getTime()));
                    }else {
                        ps.setTimestamp(i, null);
                    }
                }
            }
            int execute = ps.executeUpdate();

        }
        fileInputStream.close();
    }

    /**
     * 解析excel，构建entity，利用mapper保存数据库
     * @param entityClass    实体clazz
     * @param fileInputStream   excel文件输入流
     * @return
     */
    public static List convertExcelDataByEntity(Class entityClass, InputStream fileInputStream) throws Exception {

        //获取工作薄
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //获取需要插入的字段集
        Row fieldNames = sheet.getRow(1);
        if (fieldNames == null) {
            throw new Exception("未获取到需要导入的字段标识行，请检查导入模板");
        }
        //获取一行中有多少个单元格
        int cellCount = fieldNames.getPhysicalNumberOfCells();

        //获取表中的内容,从第3行开始是excel中需要插入数据库的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        //记录所有值的集合
//        List<Map<String,Object>> values = new ArrayList<>();
        JSONArray values = new JSONArray();
        for (int rowNum = 2; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                JSONObject jsonObject = new JSONObject();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    //获取单元
                    Cell fieldName = fieldNames.getCell(cellNum);
                    if (fieldName != null) {
                        jsonObject.put(fieldName.getStringCellValue(), getCellValue(rowData.getCell(cellNum)));
                    }
                }
                values.add(jsonObject);
            }
        }

        List entityDatas = values.toJavaList(entityClass);
 
        return entityDatas;

    }

    /**
     * 反射获取数据中指定字段的值
     *
     * @param clazz
     * @param obj
     * @param fieldName
     * @return
     */
    private static Object getMethodVal(Class<?> clazz, Object obj, String fieldName) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method getMethod = clazz.getMethod(getter);
            return getMethod.invoke(obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    private static Object getCellValue(Cell cell){
        Object value = null;
        CellType cellType = cell.getCellType();
        switch (cellType){
            case STRING://字符串
                value = cell.getStringCellValue();
                break;
            case BOOLEAN://布尔值
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC://数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)){//日期

                    value = formatter.format(cell.getDateCellValue());
                }else{
                    // 不是日期格式，则防止当数字过长时以科学计数法显示
                    cell.setCellType(CellType.STRING);
                    value = cell.toString();
                }
                break;
            case BLANK://空
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return value;
    }

    private static String getCellValueString(Cell cell) {
        Object cellValue = getCellValue(cell);
        String cellValueString = "";
        if(cellValue instanceof String){
            cellValueString = (String) cellValue;
        }else if(cellValue instanceof Boolean){
            cellValueString = String.valueOf(cellValue);
        }else if(cellValue instanceof Date) {
            cellValueString = formatter.format(cellValue);
        }else {
            cellValueString = (String) cellValue;
        }

        return cellValueString;
    }

    /**
     * 生成excel表头cell样式
     * @param workbook
     * @return
     */
    public static CellStyle getHeadCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = workbook.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        return style;
    }

    /**
     * 生成excel数据cell样式
     * @param workbook
     * @return
     */
    public static CellStyle getDataCellStyle(Workbook workbook){

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = workbook.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        return style;
    }

    /**
    设置浏览器下载响应头
    */
    public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = getFileName(request,fileName);
//                fileName = URLDecoder.decode(fileName, "UTF-8");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 下载文件名重新编码
     * @param request 请求对象
     * @param fileName 文件名
     * @return 编码后的文件名
     */
    public static String getFileName(HttpServletRequest request, String fileName) throws UnsupportedEncodingException
    {
        final String agent = request.getHeader("USER-AGENT");
        String filename = fileName;
        if (agent.contains("MSIE"))
        {
            // IE浏览器
            filename = URLEncoder.encode(filename, "utf-8");
            filename = filename.replace("+", " ");
        }
        else if (agent.contains("Firefox"))
        {
            // 火狐浏览器
            filename = new String(fileName.getBytes(), "ISO8859-1");
        }
        else if (agent.contains("Chrome"))
        {
            // google浏览器
            filename = URLEncoder.encode(filename, "utf-8");
        }
        else
        {
            // 其它浏览器
            filename = URLEncoder.encode(filename, "utf-8");
        }
        return filename;
    }

}